package org.zjvis.datascience.service;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Joiner;
import com.google.common.collect.Maps;
import javafx.util.Pair;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.ImmutableTriple;
import org.apache.commons.lang3.tuple.Triple;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.zjvis.datascience.common.constant.Constant;
import org.zjvis.datascience.common.dto.*;
import org.zjvis.datascience.common.exception.InvalidFormulaException;
import org.zjvis.datascience.common.formula.dto.FormulaDTO;
import org.zjvis.datascience.common.formula.dto.FormulaDatasetDTO;
import org.zjvis.datascience.common.dto.graph.GraphDTO;
import org.zjvis.datascience.common.enums.TaskInstanceStatus;
import org.zjvis.datascience.common.enums.TaskTypeEnum;
import org.zjvis.datascience.common.widget.dto.WidgetDTO;
import org.zjvis.datascience.common.widget.enums.WidgetTypeEnum;
import org.zjvis.datascience.common.exception.BaseErrorCode;
import org.zjvis.datascience.common.exception.DataScienceException;
import org.zjvis.datascience.common.formula.FormulaStatus;
import org.zjvis.datascience.common.formula.dto.FormulaHistoryDTO;
import org.zjvis.datascience.common.model.AggregateColumn;
import org.zjvis.datascience.common.model.AggregateResult;
import org.zjvis.datascience.common.model.ApiResultCode;
import org.zjvis.datascience.common.model.Table;
import org.zjvis.datascience.common.model.stat.ColumnAction;
import org.zjvis.datascience.common.sql.DataCleanSqlHelper;
import org.zjvis.datascience.common.util.*;
import org.zjvis.datascience.common.util.db.JDBCUtil;
import org.zjvis.datascience.common.formula.vo.FormulaHistoryVO;
import org.zjvis.datascience.common.formula.vo.FormulaRequestVO;
import org.zjvis.datascience.common.widget.vo.WidgetFormulaVO;
import org.zjvis.datascience.common.widget.vo.WidgetPackageVO;
import org.zjvis.datascience.common.widget.vo.WidgetPageVO;
import org.zjvis.datascience.common.widget.vo.WidgetVO;
import org.zjvis.datascience.common.widget.request.RelateWidgetRequestVO;
import org.zjvis.datascience.common.widget.request.RelateWidgetResultVO;
import org.zjvis.datascience.common.widget.RelateWidget;
import org.zjvis.datascience.common.widget.WidgetDTOUtil;
import org.zjvis.datascience.common.widget.enums.RelatedWidgetStatusEnum;
import org.zjvis.datascience.service.cleanup.CleanUpRecommendTablesRunner;
import org.zjvis.datascience.service.cleanup.CleanUpThreadPool;
import org.zjvis.datascience.service.dataprovider.GPDataProvider;
import org.zjvis.datascience.service.mapper.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

import static org.zjvis.datascience.common.constant.IdConstant.*;
import static org.zjvis.datascience.common.widget.WidgetJsonConstant.CHART_OPTION;
import static org.zjvis.datascience.common.widget.WidgetJsonConstant.INTERACTION_JSON;

/**
 * @description Widget 可视化图表服务 Service
 * @date 2021-12-26
 */
@Service
public class WidgetService {

    @Autowired
    private WidgetMapper widgetMapper;

    @Autowired
    DatasetMapper datasetMapper;

    @Autowired
    private GPDataProvider gpDataProvider;

    @Autowired
    private CleanUpThreadPool cleanUpThreadPool;

    @Autowired
    private UrbanDataService urbanDataService;

    @Autowired
    private GraphMapper graphMapper;

    @Autowired
    private TaskMapper taskMapper;

    //因为returnAllChildrenIds 不能一句SQL 实现 现在使用taskService 后续实现可移除
    @Lazy
    @Autowired
    private TaskService taskService;

    @Autowired
    private FormulaHistoryMapper formulaHistoryMapper;

    @Autowired
    private TaskInstanceService taskInstanceService;

    @Autowired
    private RecommendService recommendService;

    @Autowired
    private TColumnService tcolumnService;

    @Lazy
    @Autowired
    private ProjectService projectService;

    @Lazy
    @Autowired
    private DashboardService dashboardService;

    @Autowired
    public RedisUtil redisUtil;

    private final static Logger logger = LoggerFactory.getLogger(WidgetService.class);

    private final static String TEXT_PLACEHOLDER = "TEXT";

    public WidgetDTO queryById(Long id) {
        return widgetMapper.queryById(id);
    }

    public List<WidgetDTO> queryByIds(List<Long> ids) {
        return widgetMapper.queryByIds(ids);
    }

    public List<WidgetDTO> queryTemplateByTaskId(Long taskId) {
        return widgetMapper.queryTemplateByTaskId(taskId);
    }

    public List<WidgetDTO> queryByTaskIds(List<Long> taskIds) {
        return widgetMapper.queryByTaskIds(taskIds);
    }

    public List<WidgetDTO> queryByTaskIdAndType(Long taskId) {
        return widgetMapper.queryByTaskIdAndType(taskId);
    }

    public List<WidgetDTO> queryByTypeAndUser(String type, Long userId) {
        return widgetMapper.queryByTypeAndUser(type, userId);
    }

    public List<WidgetDTO> queryTemplateByDatasetId(Long datasetId) {
        return widgetMapper.queryTemplateByDatasetId(datasetId);
    }

    public Long save(WidgetDTO widget) {
        widgetMapper.save(widget);
        return widget.getId();
    }

    public void update(WidgetDTO widget) {
        widgetMapper.update(widget);
    }

    public void delete(Long id) {
        Map<String, Object> params = Maps.newHashMap();
        params.put("ids", new Long[]{id});
        widgetMapper.delete(params);
    }

    public void delete(Long userId, List<Long> ids) {
        if (CollectionUtils.isEmpty(ids)) {
            return;
        }
        Map<String, Object> params = Maps.newHashMap();
        params.put("ids", ids);
        params.put("userId", userId);
        widgetMapper.delete(params);
    }

    public void deleteByTasks(List<Long> tasks) {
        if (CollectionUtils.isEmpty(tasks)) {
            return;
        }
        widgetMapper.deleteByTasks(tasks);
    }

    public void publish(Long id) {
        WidgetDTO widget = queryById(id);
        widget.setPublishName(widget.getName());
        widget.setPublishDataJson(widget.getDataJson());
        widget.setPublishTime(LocalDateTime.now());
        update(widget);
    }

    public void check(long id) {
        WidgetDTO dto = widgetMapper.queryById(id);
        if (dto == null) {
            throw DataScienceException.of(BaseErrorCode.WIDGET_NOT_EXIST, "id:" + id);
        }
    }


    public List<WidgetDTO> queryTidByModify(String startDate) {
        return widgetMapper.queryTidByModify(startDate);
    }

    /**
     * 删除可视化后,推荐视图要删除临时表
     *
     * @param tables
     */
    private void submitCleanUpTask(List<String> tables) {
        if (ObjectUtil.isNotEmpty(tables)) {
            cleanUpThreadPool.getExecutor()
                    .submit(
                            new CleanUpRecommendTablesRunner(Joiner.on(",").join(tables), gpDataProvider));
        }
    }

    /**
     * 查询出删除task的关联可视化图表id
     *
     * @param tasks
     * @return
     */
    public List<Long> queryIdByTasks(List<Long> tasks) {
        return widgetMapper.queryIdByTasks(tasks);
    }

    @Transactional(rollbackFor = Exception.class)
    public List<Long> batchUpdate(JSONArray widgets) {
        if (CollectionUtil.isEmpty(widgets)) {
            return null;
        }
        List<Long> result = Lists.newArrayList();
        for (int i = 0; i < widgets.size(); i++) {
            WidgetVO vo = widgets.getObject(i, WidgetVO.class);
            if (vo == null || vo.getId() == null || vo.getData() == null || vo.getData()
                    .isEmpty()) {
                throw new DataScienceException(ApiResultCode.PARAM_ERROR.getMessage());
            }

            widgetMapper.update(vo.toWidget());
            result.add(vo.getId());
        }
        return result;
    }

    public boolean needConvert(JSONObject jsonObj) {
        JSONArray convert = jsonObj.getJSONArray("convert");
        if (CollectionUtil.isEmpty(convert)) {
            return false;
        }
        for (int i = 0; i < convert.size(); i++) {
            JSONObject jsonObject = convert.getJSONObject(i);
            String semantic = jsonObject.getString("semantic");
            if (!("ip".equals(semantic) || "country".equals(semantic) || "province".equals(semantic)
                    || "city".equals(semantic) || "postcode".equals(semantic))) {
                return false;
            }
        }
        return true;
    }

    /**
     * 地理或ip增加经纬度坐标
     */
    public void convertCoordinate(AggregateResult ret, JSONObject jsonObj) {
        JSONArray convert = jsonObj.getJSONArray("convert");
        List<AggregateColumn> columns = ret.getColumns();
        //语义, 列名, index
        List<Triple<String, String, Integer>> list = Lists.newArrayList();
        for (int i = 0; i < convert.size(); i++) {
            JSONObject item = convert.getJSONObject(i);
            for (AggregateColumn column : columns) {
                if (column.getName().equals(item.getString("semanticColumn"))) {
                    list.add(new ImmutableTriple<>(item.getString("semantic"),
                            item.getString("semanticColumn"), column.getIndex()));
                }
            }
        }
        Map<String, List<String>> coordinates = Maps.newHashMap();
        list.forEach(triple -> coordinates.put(triple.getMiddle(), Lists.newArrayList()));
        Object[][] data = ret.getData();
        for (Object[] datum : data) {
            for (Triple<String, String, Integer> triple : list) {
                coordinates.get(triple.getMiddle()).add(urbanDataService.getCoordinate(
                        triple.getLeft(), String.valueOf(datum[triple.getRight() - 1])));
            }
        }
        JSONObject extraData = new JSONObject();
        extraData.put("coordinates", coordinates);
        ret.setExtraData(extraData);
    }

    public List<Long> getAllWidgetIdsByDFS(Long pipelineId) {
        List<TaskDTO> allTask = taskMapper.queryByPipeline(pipelineId);
        if (CollectionUtils.isEmpty(allTask)) {
            return new ArrayList<Long>();
        }
        //排序后的taskId列表
        List<Long> dfsList = Lists.newArrayList();
        //taskId->taskDTO
        Map<Long, TaskDTO> taskMap = Maps.newHashMap();
        for (TaskDTO task : allTask) {
            taskMap.put(task.getId(), task);
        }
        //dfs遍历排序pipeline中所有节点
        for (TaskDTO task : allTask) {
            if (StringUtils.isEmpty(task.getParentId())) {
                dfsTraverse(taskMap, dfsList, task);
            }
        }
        return dfsList;
    }

    public JSONObject getGlobalWidgets(WidgetPageVO vo) {
        List<Long> taskIds = getAllWidgetIdsByDFS(vo.getPipelineId());
        //查出所有图表
        if (taskIds.size() > 0) {
            List<WidgetDTO> widgetDTOS = widgetMapper.queryByTaskIds(taskIds);
            //taskId->widgetList
            Map<Long, List<WidgetDTO>> tidMap = Maps.newHashMap();
            for (WidgetDTO widget : widgetDTOS) {
                List<WidgetDTO> widgets = tidMap.get(widget.getTid());
                if (null == widgets) {
                    List<WidgetDTO> list = Lists.newArrayList();
                    list.add(widget);
                    tidMap.put(widget.getTid(), list);
                } else {
                    widgets.add(widget);
                }
            }
            //如果选中了某个节点，重新排个序，使其前后为该节点的父子节点
            TaskDTO dto = taskMapper.queryById(vo.getTaskId());
            if (null != dto) {
                List<Long> childList = dto.getChildIdList();
                List<Long> parentList = dto.getParentIdList();

                taskIds.removeAll(childList);
                taskIds.removeAll(parentList);
                int index = taskIds.indexOf(vo.getTaskId());

                if (CollectionUtil.isNotEmpty(childList)) {
                    taskIds.addAll(index + 1, childList);
                }
                if (CollectionUtil.isNotEmpty(parentList)) {
                    taskIds.addAll(index, parentList);
                }
            }
            List<WidgetDTO> resultList = Lists.newArrayList();
            taskIds.stream().filter(tidMap::containsKey).forEach(id -> resultList.addAll(tidMap.get(id)));
            Integer startIndex = Math.min(vo.getStartIndex(), resultList.size());
            int toIndex = Math.min(startIndex + vo.getPageSize(), resultList.size());
            List<WidgetDTO> pageWidgets = resultList.subList(startIndex, toIndex);

            //包装，返回结果
            JSONObject resp = new JSONObject();
            resp.put("totalElements", widgetDTOS.size());
            resp.put("startIndex", startIndex);
            resp.put("pageSize", vo.getPageSize());

            Long tempTid = 0L;
            List<WidgetPackageVO> packages = Lists.newArrayList();
            for (WidgetDTO widget : pageWidgets) {
                if (!tempTid.equals(widget.getTid())) {
                    TaskDTO task = taskMapper.queryById(widget.getTid());
                    List<WidgetVO> widgetVOs = Lists.newArrayList();
                    widgetVOs.add(widget.view());
                    packages.add(new WidgetPackageVO(task.getId(), task.getName(), widgetVOs));
                    tempTid = widget.getTid();
                } else {
                    WidgetPackageVO packageVO = packages.get(packages.size() - 1);
                    packageVO.getWidgets().add(widget.view());
                }
            }
            resp.put("data", packages);
            return resp;
        } else {
            return new JSONObject();
        }
    }

    private void dfsTraverse(Map<Long, TaskDTO> taskMap, List<Long> result, TaskDTO task) {
        result.add(task.getId());
        String childIds = task.getChildId();
        if (StringUtils.isEmpty(childIds)) {
            return;
        }
        String[] split = childIds.split(",");
        for (String childId : split) {
            Long childTaskId = Long.valueOf(childId);
            if (result.contains(childTaskId)) {
                continue;
            }
            if (taskMap.get(childTaskId) == null) {
                continue;
            }
            dfsTraverse(taskMap, result, taskMap.get(childTaskId));
        }
    }

    public void deleteGraphAnalysisById(Long graphId) {
        GraphDTO graph = graphMapper.queryById(graphId);
        JSONObject obj = JSONObject.parseObject(graph.getDataJson());
        JSONArray widgets = obj.getJSONArray("widgets");
        if (CollectionUtil.isNotEmpty(widgets)) {
            List<Long> ids = widgets.toJavaList(Long.class);
            this.delete(null, ids);
        }
    }

    @Transactional(rollbackFor = Exception.class)
    public List<Long> batchInsert(List<Long> need2processIds, Map<Long, Long> relationshipMap) {
        List<Long> result = new ArrayList<>();
        for (Long id : need2processIds) {
            WidgetDTO dto = widgetMapper.queryById(id);
            dto.setId(null);
            dto.setPublishName(null);
            dto.setPublishTime(null);
            dto.setPublishDataJson(null);
            dto.setGmtCreator(JwtUtil.getCurrentUserId());
            dto.setGmtModifier(JwtUtil.getCurrentUserId());
            if (null != relationshipMap) {
                //需要解析datajson 替换对应的taskId
                JSONObject tempJsonObj = JSONObject.parseObject(dto.getDataJson());
                if (tempJsonObj.containsKey(CHART_OPTION)) {
                    JSONObject chartOptions = tempJsonObj.getJSONObject(CHART_OPTION);
                    if (chartOptions.containsKey(TASK_ID)) {
                        Long newTid = relationshipMap.get(chartOptions.getLong(TASK_ID));
                        chartOptions.put(TASK_ID, newTid);
                        dto.setTid(newTid);
                    }
                    if (chartOptions.containsKey(DATA_ID)) {
                        try {
                            Long newTid = relationshipMap.get(chartOptions.getLong(DATA_ID));
                            chartOptions.put(DATA_ID, newTid);
                            dto.setTid(newTid);
                        }catch (JSONException e){
                            logger.warn("replace dataId {} failed when copy widgets,", chartOptions.getObject(DATA_ID, Object.class));
                        }
                    }
                    if (chartOptions.containsKey(PIPELINE_ID)){
                        chartOptions.put(PIPELINE_ID, relationshipMap.get(PIPELINE_DUMMY_IDX));
                    }
                    tempJsonObj.put(CHART_OPTION, chartOptions);
                }
                dto.setDataJson(tempJsonObj.toJSONString());
            }
            widgetMapper.save(dto);
            result.add(dto.getId());
        }
        return result;
    }

    public WidgetFormulaVO executeFormula(String tableName, String formula, Integer precisionNum) {
        WidgetFormulaVO vo = new WidgetFormulaVO();
        precisionNum = null == precisionNum ? 3 : precisionNum; //默认保留两位小数
        tableName = ToolUtil.alignTableName(tableName, 0L);
        Table table = new Table(1L, tableName);
        Map<String, Integer> columnTypes = gpDataProvider.getColumnTypesOriginal(table);
        String sql;
        Connection conn = null;
        Statement st;
        ResultSet rs;
        Set<String> resultSet = new HashSet<>();
        try {
            sql = DataCleanSqlHelper
                    .getWidgetFormulaSelectSql(columnTypes, tableName, formula);
            if (StringUtils.isNotEmpty(sql)) {
                conn = gpDataProvider.getConn(1L);
                st = conn.createStatement();
                rs = st.executeQuery(sql);
                while (rs.next()) {
                    resultSet.add(rs.getString(1));
                }
            }
            if (resultSet.size() != 1) {
                throw new Exception("返回结果不唯一！");
            }
            String result = resultTrim(resultSet.toArray()[0].toString(), precisionNum);
            vo.setResult(result);
            vo.setLogInfo(null);
            vo.setStatus(TaskInstanceStatus.SUCCESS.toString());
        } catch (InvalidFormulaException e) {
            vo.setStatus(TaskInstanceStatus.FAIL.toString());
            logger.warn("error happened when execute formula {}, since {}", formula, e.getMessage());
            vo.setLogInfo(e.getMessage());
        } catch (Exception e1) {
            vo.setStatus(TaskInstanceStatus.FAIL.toString());
            logger.warn("error happened when execute formula {}, since {}", formula, e1.getMessage());
            vo.setLogInfo("待执行的公式存在错误。");
        } finally {
            JDBCUtil.close(conn, null, null);
        }
        return vo;
    }

    private static String resultTrim(String result, Integer precisionNum){
        if (result.split("\\.").length == 1) {
            result += ".";
        }
        //填充0 的 最长长度是10
        result = String.format("%-" + (result.length() + 10) + "s", result).replace(" ", "0");
        if (precisionNum.equals(0)) {
            result = result.split("\\.")[0];
        } else {
            result = result.substring(0, result.indexOf('.') + precisionNum + 1);
        }
        return result;
    }

    public WidgetFormulaVO addOrUpdateFormula(FormulaHistoryDTO formulaHistoryDTO, String operator) {
        WidgetFormulaVO vo = new WidgetFormulaVO();
        String tableName = getAssociateTableName(WidgetTypeEnum.valueOf(
                StringUtils.upperCase(formulaHistoryDTO.getType())), formulaHistoryDTO.getTaskId());
        if (tableName == null || tableName.length() == 0) {
            vo.setStatus(TaskInstanceStatus.FAIL.toString());
            vo.setLogInfo(ApiResultCode.DATA_NULL.getMessage());
            return vo;
        }
        vo = executeFormula(tableName, formulaHistoryDTO.getFormula(), formulaHistoryDTO.getPrecisionNum());

        try {
            if (null != vo && vo.getStatus().equals(TaskInstanceStatus.SUCCESS.toString())) {
                formulaHistoryDTO.setResult(vo.getResult());
                if (operator.equals("add")) {
                    formulaHistoryMapper.addFormula(formulaHistoryDTO);
                } else {
                    formulaHistoryMapper.updateFormula(formulaHistoryDTO);
                }
                vo.setFormulaId(formulaHistoryDTO.getId());
            }
        } catch (Exception e) {
            throw DataScienceException.of(BaseErrorCode.FORMULA_UPDATE_FAILED);
        }

        return vo;
    }

    public WidgetFormulaVO deleteFormula(long formulaId) {
        WidgetFormulaVO vo = new WidgetFormulaVO();
        if (formulaHistoryMapper.deleteFormula(formulaId) != 1) {
            vo.setStatus(TaskInstanceStatus.FAIL.toString());
            vo.setLogInfo(String.format(Constant.errorTpl, "删除出错！"));
        } else {
            vo.setLogInfo(null);
            vo.setStatus(TaskInstanceStatus.SUCCESS.toString());
        }
        return vo;
    }

    public FormulaHistoryVO queryFormulasByProjectId(FormulaRequestVO requestVO) {
        if (requestVO.getPipelineId() == null) {
            requestVO.setPipelineId(projectService.getDefaultPipelineId(requestVO.getProjectId()));
        }
        List<FormulaHistoryDTO> historyDTOList = formulaHistoryMapper.queryFormulasByProjectId(requestVO.getProjectId());

        List<FormulaDatasetDTO> formulaDatasetDTOS = new ArrayList<>();
        Map<Long, List<FormulaDTO>> formulaMap = Maps.newHashMap();
        Map<String, Long> relationshipMap = requestVO.getRelationshipMap();

        Map<Long, String> nameMap = new HashMap<>();
        Map<Long, String> typeMap = new HashMap<>();

        for (FormulaHistoryDTO formulaHistoryDTO : historyDTOList) {
            FormulaDTO formulaDTO = formulaHistoryDTO.toFormulaDTO();
            String status = StringUtils.EMPTY;
            long taskId = formulaHistoryDTO.getTaskId();
            if (null != relationshipMap && relationshipMap.containsKey(String.valueOf(taskId))) {
                taskId = relationshipMap.get(String.valueOf(taskId));
            }
            String realTable = getAssociateTableName(WidgetTypeEnum.valueOf(
                    StringUtils.upperCase(formulaHistoryDTO.getType())), taskId);
            if (realTable == null || realTable.length() == 0) {
                status = FormulaStatus.DELETED.getValue();
            } else if (realTable.equals(formulaHistoryDTO.getTableName())) {
                status = FormulaStatus.ORIGINAL.getValue();
                formulaDTO.setResult(formulaHistoryDTO.getResult());
            } else {
                WidgetFormulaVO vo = executeFormula(realTable, formulaHistoryDTO.getFormula(), formulaHistoryDTO.getPrecisionNum());
                if (vo.getStatus().equals(TaskInstanceStatus.SUCCESS.toString())) {
                    status = FormulaStatus.UPDATED_SUCCESS.getValue();
                    formulaDTO.setResult(vo.getResult());
                    formulaHistoryDTO.setResult(vo.getResult());
                } else {
                    status = FormulaStatus.UPDATED_FAILED.getValue();
                    formulaDTO.setResult(vo.getLogInfo());
                    formulaHistoryDTO.setResult(vo.getLogInfo());
                }
                formulaHistoryDTO.setTableName(realTable);
                formulaHistoryMapper.updateFormula(formulaHistoryDTO);
            }
            formulaDTO.setStatus(status);
            List<FormulaDTO> formulaList = formulaMap.getOrDefault(taskId, Lists.newArrayList());
            formulaList.add(formulaDTO);
            formulaMap.put(taskId, formulaList);
            nameMap.put(taskId, formulaHistoryDTO.getName());
            typeMap.put(taskId, formulaHistoryDTO.getType());
        }

        for (long key : formulaMap.keySet()) {
            formulaDatasetDTOS.add(FormulaDatasetDTO.builder()
                    .taskId(key)
                    .name(nameMap.get(key))
                    .type(typeMap.get(key))
                    .formulaList(formulaMap.get(key))
                    .build());
        }

        return new FormulaHistoryVO(requestVO.getProjectId(), formulaDatasetDTOS);
    }


    /**
     * 根据图表widget类型的不同获得不同数据表
     *
     * @param typeEnum
     * @param id
     * @return
     */
    public String getAssociateTableName(WidgetTypeEnum typeEnum, Long id) {
        String tableName = StringUtils.EMPTY;
        try {
            if (typeEnum.equals(WidgetTypeEnum.DATA) || typeEnum.equals(WidgetTypeEnum.DATASET)) {
                DatasetDTO dataset = datasetMapper.queryById(id);
                if (dataset == null) {
                    return StringUtils.EMPTY;
                }
                tableName = DatasetUtil.extractTableStr(dataset);
            } else if (typeEnum.equals(WidgetTypeEnum.TASK) || typeEnum.equals(WidgetTypeEnum.TASK_COPY)) {
                TaskDTO task = taskMapper.queryById(id);
                if (TaskTypeEnum.TASK_TYPE_DATA.getVal().equals(task.getType())) {
                    tableName = TaskUtil.extractTableStr(task);
                } else {
                    TaskInstanceDTO taskInstance = taskInstanceService.queryLatestInstanceForTask(id);
                    if (null != taskInstance) {
                        List<String> outputTables = taskInstanceService.queryByTableName(taskInstance);
                        if (CollectionUtils.isNotEmpty(outputTables)) {
                            tableName = outputTables.get(0);
                        }
                    } else {
                        // 如果没有taskInstance, 说明没有执行，取taskDTO中对应的表
                        tableName = TaskUtil.extractTableStr(task);
                    }
                }
            } else if (typeEnum.equals(WidgetTypeEnum.RECOMMEND)) {
                List<String> outputTables = recommendService.queryTableNameById(id);
                if (CollectionUtils.isNotEmpty(outputTables)) {
                    tableName = outputTables.get(0);
                }
            } else if (typeEnum.equals(WidgetTypeEnum.TCLEAN)) {
                //对应的是clean节点中 每个清洗记录 叫做 tclean
                ColumnAction columnAction = tcolumnService.queryActionById(id);
                JSONObject columnActionJson = columnAction.getData();
                if (columnActionJson != null && !columnActionJson.isEmpty()) {
                    tableName = columnAction.getData().getString("table");
                }
            } else if (typeEnum.equals(WidgetTypeEnum.TEXT) || typeEnum.equals(WidgetTypeEnum.FILTER_FORM)) {
                //文本控件, 不需要获取JDBC连接 直接返回，通过widgetService查
                return TEXT_PLACEHOLDER;
            }
        } catch (Exception e) {
            return "";
        }
        return tableName;
    }

    /**
     * 在pipeline中获取相关联的图表widget list
     *
     * @param vo
     * @return
     */
    public RelateWidgetResultVO getRelateWidgetsInPipeline(RelateWidgetRequestVO vo) {
        RelateWidgetResultVO relatedWidgetResult = null;
        WidgetDTO targetWidget = widgetMapper.queryById(vo.getWidgetId());

        if (!targetWidget.isDataTableType()) {//数据表格类型将不显示关联图表，只能被关联
            List<WidgetDTO> currentWidgets = dashboardService.returnAllWidgetInBoard(vo.getDashboardId());//现在画布中的内容
            currentWidgets.removeIf(WidgetDTO::isTextType);//默认不关联 TEXT 类型
            currentWidgets.removeIf(WidgetDTO::isFilterForm);//不关联筛选器控件
            currentWidgets.removeIf(candidate -> candidate.getId().equals(vo.getWidgetId())); //默认跟自己不关联
            List<Long> usedTaskIds = currentWidgets.stream().map(WidgetDTO::getTid).collect(Collectors.toList()); //现在画布中使用的taskIds
            List<Long> currentWidgetIds = currentWidgets.stream().map(WidgetDTO::getId).collect(Collectors.toList());

            JSONObject cachedResult = WidgetDTOUtil.getValueByKey(INTERACTION_JSON, targetWidget, JSONObject.class);
            List<Long> targetChildrenIds = taskService.returnAllChildrenIds(targetWidget.getTid());

            if (null != cachedResult && cachedResult.size() > 0) {
                Map<Long, RelateWidget> cachedWidgetsMap = null;
                relatedWidgetResult = cachedResult.toJavaObject(RelateWidgetResultVO.class);
                if (null != relatedWidgetResult.getRelateWidgets()) {
                    cachedWidgetsMap = relatedWidgetResult.getRelateWidgets().stream().collect(Collectors.toMap(RelateWidget::getWidgetId, e -> e));
                }
                //如果之前有记录，就检查一次各个widget的 xAttr 是否满足状态 然后 更新状态并返回

                List<RelateWidget> newWidgetsResult = Lists.newArrayList();
                for (WidgetDTO tempWidget : currentWidgets) {
                    if (null != cachedWidgetsMap) {
                        if (cachedWidgetsMap.containsKey(tempWidget.getId())) {
                            //检查状态
                            RelateWidget tempCachedWidget = RelateWidget.syncProp(cachedWidgetsMap.get(tempWidget.getId()), tempWidget);
                            if (this.isRelated(targetWidget.getTid(), tempCachedWidget.getTaskId(), targetWidget.getXAttrInfo())) {
                                //如果还是相关的 就保持状态 AVAILABLE || CONNECTED 但如果 之前是LOSE_CONNECTION 就要重置状态
                                if (tempCachedWidget.isLoseConnected()) {
                                    tempCachedWidget.setStatus(RelatedWidgetStatusEnum.CONNECTED.getDesc());
                                }
                            } else {
                                //如果已经不相关了，在CONNECTION 状态下， 置灰
                                if (tempCachedWidget.isConnected()) {
                                    tempCachedWidget.setStatus(RelatedWidgetStatusEnum.LOSE_CONNECTION.getDesc());
                                } else {
                                    if (!currentWidgetIds.contains(tempCachedWidget.getWidgetId())) {
                                        //两个图表 不相关了 但是还存在画布上，不删除， 不在画布上才删除
                                        break;
                                    }
                                }
                            }
                            newWidgetsResult.add(tempCachedWidget);
                        } else {
                            //再补充一些新添加的widget 而且 必须是下游节点的widget
                            if (targetChildrenIds.contains(tempWidget.getTid()) &&
                                    this.isRelated(targetWidget.getTid(), tempWidget.getTid(), targetWidget.getXAttrInfo())) {
                                newWidgetsResult.add(tempWidget.toRelatedWidget(RelatedWidgetStatusEnum.AVAILABLE));
                            }
                        }
                    }
                }

                //再剔除，不在现在画布中的widget
                newWidgetsResult.removeIf(next -> !currentWidgetIds.contains(next.getWidgetId()));

                Collections.sort(newWidgetsResult, Comparator.comparingLong(RelateWidget::getWidgetId));
                relatedWidgetResult.setRelateWidgets(newWidgetsResult);

            } else {
                //如果之前没有，查 当前节点和 后续节点的 在 当前画布 中图表信息 直接返回
                usedTaskIds.retainAll(targetChildrenIds); //使用的task  和 全部子task节点 取交集， 遍历交集中所有task对应的图表
                if (usedTaskIds.size() > 0) {
                    List<WidgetDTO> allPossibleWidgets = widgetMapper.queryAllTypeByTaskIds(usedTaskIds);
                    allPossibleWidgets.retainAll(currentWidgets);
                    allPossibleWidgets.removeIf(candidate -> !this.isRelated(targetWidget.getTid(), candidate.getTid(), targetWidget.getXAttrInfo()));
                    Collections.sort(allPossibleWidgets, Comparator.comparingLong(WidgetDTO::getId));
                    relatedWidgetResult = WidgetDTOUtil.wrapRelatedWidgetResult(targetWidget, allPossibleWidgets, RelatedWidgetStatusEnum.AVAILABLE);
                }
            }

            WidgetDTOUtil.updateJsonWithSpecificKey(targetWidget, INTERACTION_JSON, relatedWidgetResult);
            widgetMapper.update(targetWidget);
        }
        return relatedWidgetResult;
    }

    /**
     * 判断widget A B 是否相关
     *
     * @param taskId1   来自A widget
     * @param taskId2   来自B widget
     * @param xAttrInfo 来自A widget < colName, colType >
     * @return
     */
    private boolean isRelated(Long taskId1, Long taskId2, Pair<String, String> xAttrInfo) {
        if (taskId1.equals(taskId2)) return true;
        List<String> tableCols = TaskDTOUtil.getValueByKey("output[0].tableCols", taskMapper.queryById(taskId2), JSONArray.class).toJavaList(String.class);
//            List<String> tableCols = taskMapper.queryJsonArrayByIdAndKey(taskId2, "output[0].tableCols").toJavaList(String.class); //NAP-3927
        return tableCols.contains(xAttrInfo.getKey());
    }

    /**
     * 判断 筛选器控件 与 widget A 是否相关
     *
     * @param taskId1 来自 筛选器控件
     * @param taskId2 来自A widget
     * @return
     */
    private boolean isRelatedInFilter(Long taskId1, Long taskId2) {
        return taskId1.equals(taskId2);
    }

    /**
     * 筛选器控件奇葩 -》 关联图表内容仅拉出当前数据集绘制出的图表
     * 跟widget之间的图表关联不同， 筛选器在配置的时候（没有widgetId的阶段） 就要找到相关可以关联的widget
     *
     * @param vo
     * @return
     */
    public RelateWidgetResultVO getRelatedWidgetsWithinSpecificDataNode(RelateWidgetRequestVO vo) {
        List<WidgetDTO> currentWidgets = dashboardService.returnAllWidgetInBoard(vo.getDashboardId());//现在画布中的内容
        currentWidgets.removeIf(WidgetDTO::isTextType);//默认不关联 TEXT 类型
        currentWidgets.removeIf(WidgetDTO::isFilterForm);//不关联筛选器控件
        currentWidgets.removeIf(candidate -> candidate.getId().equals(vo.getWidgetId())); //默认跟自己不关联
        List<Long> usedTaskIds = currentWidgets.stream().map(WidgetDTO::getTid).collect(Collectors.toList()); //现在画布中使用的taskIds
        List<Long> currentWidgetIds = currentWidgets.stream().map(WidgetDTO::getId).collect(Collectors.toList());
        List<Long> targetChildrenIds = Lists.newArrayList();
        targetChildrenIds.add(vo.getDataId());
        if (vo.getDataType().equals("task")) {
            targetChildrenIds.addAll(taskService.returnAllChildrenIds(vo.getDataId()));
        }

        JSONObject cachedResult = null;
        WidgetDTO targetWidget = null;
        RelateWidgetResultVO relatedWidgetResult = null;
        //初始化阶段没有widgetId
        if (null != vo.getWidgetId()) {
            targetWidget = widgetMapper.queryById(vo.getWidgetId());
            cachedResult = WidgetDTOUtil.getValueByKey(INTERACTION_JSON, targetWidget, JSONObject.class);

        }

        if (null != cachedResult && cachedResult.size() > 0) {
            Map<Long, RelateWidget> cachedWidgetsMap = null;
            //如果之前有关联的历史，先创建一个映射map
            relatedWidgetResult = cachedResult.toJavaObject(RelateWidgetResultVO.class);
            if (null != relatedWidgetResult.getRelateWidgets()) {
                cachedWidgetsMap = relatedWidgetResult.getRelateWidgets().stream().collect(Collectors.toMap(RelateWidget::getWidgetId, e -> e));
            }
            //检查一次各个widget的 xAttr 是否满足状态 然后 更新状态并返回
            List<RelateWidget> newWidgetsResult = Lists.newArrayList();
            for (WidgetDTO tempCurrWidget : currentWidgets) {
                if (null != cachedWidgetsMap) {
                    if (cachedWidgetsMap.containsKey(tempCurrWidget.getId())) {
                        //cache中 存在的widget 需要检查状态
                        RelateWidget tempCachedWidget = cachedWidgetsMap.get(tempCurrWidget.getId());
                        if (!this.isRelatedInFilter(targetWidget.getTid(), tempCachedWidget.getTaskId())) {
                            //如果已经不相关了，但原来在 在FILTER_CONNECTION 状态下， 需要置灰
                            if (tempCachedWidget.isConnected()) {
                                tempCachedWidget.setStatus(RelatedWidgetStatusEnum.LOSE_CONNECTION.getDesc());
                            } else {
                                //如果已经不相关了，原来状态也是 非关联状态 就不动， 最多判断一次 是否需要删除
                                if (!currentWidgetIds.contains(tempCachedWidget.getWidgetId())) {
                                    //两个图表 不相关了 但是还存在画布上，不删除， 不在画布上才删除
                                    break;
                                }
                            }
                        }
                        newWidgetsResult.add(tempCachedWidget);
                    } else {
                        //cache中 不存在的widget， 但是再补充一些新添加的widget 而且 必须是 使用相同数据集ID 的图表
                        if (this.isRelatedInFilter(targetWidget.getTid(), tempCurrWidget.getTid())) {
                            newWidgetsResult.add(tempCurrWidget.toRelatedWidget(RelatedWidgetStatusEnum.FILTER_CONNECTED));
                        }
                    }
                }
            }

            //再剔除，不在现在画布中的widget
            newWidgetsResult.removeIf(next -> !currentWidgetIds.contains(next.getWidgetId()));

            Collections.sort(newWidgetsResult, Comparator.comparingLong(RelateWidget::getWidgetId));
            relatedWidgetResult.setRelateWidgets(newWidgetsResult);

        } else {
            //如果之前没有，使用的是dataset 就只有他自己，如果是pipeline 则还要包括其子节点
            usedTaskIds.retainAll(targetChildrenIds);
            if (usedTaskIds.size() > 0) {
                List<WidgetDTO> allPossibleWidgets = widgetMapper.queryAllTypeByTaskIds(usedTaskIds);
                allPossibleWidgets.retainAll(currentWidgets);
                allPossibleWidgets.sort(Comparator.comparingLong(WidgetDTO::getId));
                relatedWidgetResult = WidgetDTOUtil.wrapRelatedWidgetResult(targetWidget, allPossibleWidgets, RelatedWidgetStatusEnum.FILTER_CONNECTED);
            }
        }

        // 初始化阶段 没有widgetId 也就没有targetWidget
        if (ObjectUtil.isNotNull(targetWidget)) {
            WidgetDTOUtil.updateJsonWithSpecificKey(targetWidget, INTERACTION_JSON, relatedWidgetResult);
            widgetMapper.update(targetWidget);
        }
        return relatedWidgetResult;
    }

}
